﻿

CREATE VIEW [dbo].[fares_DocumenteTVADimensiuni]
AS
SELECT     a.IDDocument, a.NrDoc, a.DataDoc, a.CodUnitate, a.IDTipDocument, a.NrContract, a.DataContract, a.Observatii, a.DataScadenta, a.NrDocReceptie, a.DataDocReceptie, dbo.TipDocument.TipOperatie, 
                      dbo.TipDocument.Denumire, c.valoare1, c.valoare2, c.valoare3, c.valoare4, p.Denumire AS DenPartener, CASE WHEN dd.CotaTVA = 24 THEN dd.ValoareTVA ELSE 0 END AS ValoareTVA24, 
                      CASE WHEN dd.CotaTVA = 9 THEN dd.ValoareTVA ELSE 0 END AS ValoareTVA9, CASE WHEN dd.CotaTVA = 24 THEN CASE WHEN a.IDTipDocument IN (10, 11) 
                      THEN dd.ValoareTotala - dd.ValoareTVA ELSE dd.ValoareTotala END ELSE 0 END AS ValoareTotala24, CASE WHEN dd.CotaTVA = 9 THEN CASE WHEN a.IDTipDocument IN (10, 11) 
                      THEN dd.ValoareTotala - dd.ValoareTVA ELSE dd.ValoareTotala END ELSE 0 END AS ValoareTotala9, CASE WHEN dd.CotaTVA = 0 THEN CASE WHEN a.IDTipDocument IN (10, 11) 
                      THEN dd.ValoareTotala - dd.ValoareTVA ELSE dd.ValoareTotala END ELSE 0 END AS ValoareTotalaScutit, dd.CotaTVA, CASE WHEN (dd.CotaTVA = 24 OR
                      dd.CotaTVA = 0) THEN dd.ValoareTotalaCuAdaos ELSE 0 END AS ValoareTotalaCuAdaos24, CASE WHEN dd.CotaTVA = 9 THEN dd.ValoareTotalaCuAdaos ELSE 0 END AS ValoareTotalaCuAdaos9, 
                      CASE WHEN dd.CotaTVA = 0 THEN (dd.ValoareTotalaCuAdaos * 100) / (s.CotaTVAImplicita + 100) - dd.ValoareTotala ELSE CASE WHEN a.IDTipDocument IN (10, 11) 
                      THEN (dd.ValoareTotalaCuAdaos * 100) / (dd.CotaTVA + 100) - (dd.ValoareTotala * 100) / (dd.CotaTVA + 100) ELSE (dd.ValoareTotalaCuAdaos * 100) / (dd.CotaTVA + 100) 
                      - dd.ValoareTotala END END AS Adaos, CASE WHEN dd.CotaTVA = 0 THEN (dd.ValoareTotalaCuAdaos * s.CotaTVAImplicita) / (s.CotaTVAImplicita + 100) ELSE (dd.ValoareTotalaCuAdaos * dd.CotaTVA) 
                      / (dd.CotaTVA + 100) END AS TVANeexigibil, ar.Denumire AS DenArticol, p.IDPartener, ar.IDArticol, dd.IDRegimFiscal
FROM         dbo.Documente AS a INNER JOIN
                      dbo.Parteneri AS p ON a.IDPartener = p.IDPartener INNER JOIN
                      dbo.TipDocument ON a.IDTipDocument = dbo.TipDocument.IDTipDocument LEFT OUTER JOIN
                      dbo.fares_DocumenteDimensiuniLinie AS c ON a.IDDocument = c.IDCheieTabela AND a.CodUnitate = c.CodUnitate INNER JOIN
                      dbo.DocumenteDetaliu AS dd ON a.IDDocument = dd.IDDocument INNER JOIN
                      dbo.Articole AS ar ON dd.IDArticol = ar.IDArticol INNER JOIN
                      dbo.FaresSetup AS s ON a.CodUnitate = s.CodUnitate